using System;
using System.Collections.Generic;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Windows.Forms;

namespace DailyTransaction
{
    class DAO_CMSI
    {
        private string connectionString;
        //private SqlConnection con;
        private OleDbConnection con;
        public bool connect(string type)
        {
            //connectionString = ConfigurationManager.ConnectionStrings["SQLServer"].ConnectionString;
            if (type == "SUN")
                connectionString = GlobalClass.connectionStringSUN;
            else if (type=="CMSI")
                connectionString = GlobalClass.connectionStringACC;

            if (connectionString == "")
            {
                MessageBox.Show("Connection string is null!");
                return false;
            }

            try
            {
                if (con == null)
                {
                    con = new OleDbConnection(connectionString);
                    con.Open();
                }
                else if (con.State == ConnectionState.Closed)
                    con.Open();
            }
            catch (OleDbException ole)
            {
                MessageBox.Show(ole.Message);
                return false;
            }
            return true;
        }
        public void disconnect()
        {
            try
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }
            catch (OleDbException ole)
            { }
            finally
            {
                con.Close();
                con.Dispose();
                con = null;
            }
        }

        public int executeNonquery(string sql, string con_type)
        {
            if (connect(con_type) == false)
                return -1;
            int rslt = 0;
            try
            {
                OleDbCommand cmd = new OleDbCommand(sql);
                cmd.Connection = con;
                rslt = cmd.ExecuteNonQuery();
                disconnect();
            }
            catch (OleDbException oe)
            {
                return -1;
            }
            return rslt;
        }

        public DataTable executeQuery(string sql,string con_type)
        {
            if (connect(con_type) == false)
                return null;
            DataTable tbl = null;
            try
            {
                OleDbDataAdapter adap = new OleDbDataAdapter(sql, con);
                tbl = new DataTable();
                adap.Fill(tbl);                
                disconnect();
            }
            catch (OleDbException oe)
            {
                return null;
            }
            return tbl;
        }

        internal int update_pmnt_mode_CMSI(string pDatevisit, string pInvnumber,string pVAT_revised, string pPmnt_revised,string pUpd_pmnt_date)
        {
            int num = 0;
            string sql = "Update NEW_REVENUE set ";
            if (pVAT_revised.Equals("") == false)
            {
                sql += " UPD_VAT_NUM='" + pVAT_revised + "'";
                if (pPmnt_revised.Equals("") == false)
                    sql += ",";
            }
            if (pPmnt_revised.Equals("") == false)
            {
                sql += " UPD_PAY_ACCTCODE='" + pPmnt_revised + "'";
                //added on 24/08/2009
                sql += ", UPD_PAYMENT_DATE=#" + pUpd_pmnt_date + "#";
                //end
            }
            

            sql += " where date_visit=#" + pDatevisit + "# and INV_NBER='" + pInvnumber + "'";

            if (connect("CMSI") == false)
                return -1;    
            num = executeNonquery(sql,"CMSI");
            if (num == -1)
                return -1;
            if (connect("SUN") == false)
                return -1;
            num = executeNonquery(sql,"SUN");
            return num;
        }
    }
}
